
clear
*RESHAPE JAFFE DATA
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\2 intermediate - R code Jaffe\output"
use spillsic
rename ciq cusip
reshape long xrd, i(cusip) j(year)
rename xrd spillsic

cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\3 intermediate - format\output"
saveold spillsic_fmt, replace

forvalues i = 1984(1)1999 {
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\2 intermediate - R code Jaffe\output"
use spilltec`i'
rename ciq cusip
reshape long xrd, i(cusip) j(year)
rename xrd spilltec`i'

cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\3 intermediate - format\output"
saveold spilltec`i'_fmt, replace

*MERGE BLOOM RESULTS WITH OUR OWN
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\3 intermediate - format\output"
merge 1:1 cusip year using spillsic_fmt, nogenerate

rename spillsic spillsic_ciq
rename spilltec`i' spilltec`i'_ciq

cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\3 intermediate - format\output"
saveold spillover`i'_ciq, replace
}

*GENERATE LAGGED LOG VALUES
forvalues i = 1984(1)1999 {
clear
use "C:\Users\rthavarajah\Desktop\Paper Stuff\From Bloom Website\bsv_replication\spillovers.dta"
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\3 intermediate - format\output"
merge 1:1 cusip year using spillover`i'_ciq

rename spilltec`i'_ciq spilltec_ciq
replace spillsic_ciq = . if spillsic_ciq == 0
replace spilltec_ciq = . if spilltec_ciq == 0
gen lgspillsic_ciq = log(spillsic_ciq)
gen lgspilltec_ciq = log(spilltec_ciq)
gsort +cusip +year
gen lgspillsic_ciq1 = lgspillsic_ciq[_n-1] if cusip == cusip[_n-1]
gen lgspilltec_ciq1 = lgspilltec_ciq[_n-1] if cusip == cusip[_n-1]

order _merge spilltec_ciq spillsic_ciq lgspilltec_ciq lgspillsic_ciq lgspillsic_ciq1 lgspilltec_ciq1

cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\4 output - jaffe"
saveold spillover_augmented`i', replace

}

clear
*RESHAPE MAHALNANOBIS DATA
forvalues i = 1984(1)1999 {
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\5 intermediate - R code Mahalanobis\output"
use spilltec_mal`i'
rename ciq cusip
reshape long xrd, i(cusip) j(year)
rename xrd spilltecmal_ciq`i'

cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\6 intermediate - format\output"
saveold spilltec`i'_fmt, replace

*MERGE BLOOM RESULTS WITH OUR OWN 
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\6 intermediate - format\output"
merge 1:1 cusip year using spillsic_fmt, nogenerate
saveold spillovermal`i'_ciq, replace
}

*GENERATE LAGGED LOG VALUES
forvalues i = 1984(1)1999 {
clear
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\4 output - jaffe\"
use spillover_augmented`i'
drop _merge
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\6 intermediate - format\output"
merge 1:1 cusip year using spillovermal`i'_ciq
rename spillsicmal_ciq spillmalsic_ciq
rename spilltecmal_ciq`i' spillmaltec_ciq
gen lgspillmalsic_ciq = log(spillmalsic_ciq)
gen lgspillmaltec_ciq = log(spillmaltec_ciq)
gsort +cusip +year
gen lgspillmalsic_ciq1 = lgspillmalsic_ciq[_n-1] if cusip==cusip[_n-1]
gen lgspillmaltec_ciq1 = lgspillmaltec_ciq[_n-1] if cusip==cusip[_n-1]

order _merge spillmalsic_ciq spillmaltec_ciq lgspillmalsic_ciq lgspillmaltec_ciq lgspillmalsic_ciq1 lgspillmaltec_ciq1 

replace spillmalsic_ciq = . if spillmalsic_ciq == 0
replace spillmaltec_ciq = . if spillmaltec_ciq == 0

replace spillsic_ciq = . if spillsic_ciq == 0
replace spilltec_ciq = . if spilltec_ciq == 0
drop num
egen num = group(cusip)

cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\7 output - jaffe and mahalanobis combined\"
saveold spillover_final`i', replace
}

*scatter spillmaltec_ciq spillmaltec || lfit spillmaltec_ciq spillmaltec
*scatter spillmalsic_ciq spillmalsic || lfit spillmalsic_ciq spillmalsic
*scatter spilltec_ciq spilltec || lfit spilltec_ciq spilltec
*scatter spillsic_ciq spillsic || lfit spillsic_ciq spillsic

*correlate spillmaltec_ciq spillmaltec
*correlate spillmalsic_ciq spillmalsic
*correlate spilltec_ciq spilltec
*correlate spillsic_ciq spillsic

*GENERATE ROLLING SPILLTEC 
clear
cd "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\7 output - jaffe and mahalanobis combined"
use spillover_final

drop spillmaltec_ciq lgspillmaltec_ciq lgspillmaltec_ciq1 spilltec_ciq lgspilltec_ciq lgspilltec_ciq1

forvalues i = 1984(1)1999 {
merge 1:1 cusip year using spillover_final`i', keep(1 3) nogenerate keepusing(spillmaltec_ciq lgspillmaltec_ciq lgspillmaltec_ciq1 spilltec_ciq lgspilltec_ciq lgspilltec_ciq1)
rename spillmaltec_ciq spillmaltec_ciq`i' 
rename lgspillmaltec_ciq lgspillmaltec_ciq`i'
rename lgspillmaltec_ciq1 lgspillmaltec_ciq1`i'
rename spilltec_ciq spilltec_ciq`i'
rename lgspilltec_ciq lgspilltec_ciq`i'
rename lgspilltec_ciq1 lgspilltec_ciq1`i'
}

drop _merge
merge 1:1 cusip year using "C:\Users\rthavarajah\Desktop\Paper Stuff\Bloom Master\Spillovers Computation\7 output - jaffe and mahalanobis combined\spillover_final.dta", keep(1 3) nogenerate keepusing(spillmaltec_ciq lgspillmaltec_ciq lgspillmaltec_ciq1 spilltec_ciq lgspilltec_ciq lgspilltec_ciq1)

gen spilltec_roll = spilltec_ciq1997
gen lgspilltec_roll = lgspilltec_ciq1997
gen lgspilltec_roll1 = lgspilltec_ciq11997
gen spillmaltec_roll = spillmaltec_ciq1997
gen lgspillmaltec_roll = lgspillmaltec_ciq1997
gen lgspillmaltec_roll1 = lgspillmaltec_ciq11997

forvalues i = 1984(1)1997 {
replace spilltec_roll = spilltec_ciq`i' if year == `i' - 1
replace lgspilltec_roll = lgspilltec_ciq`i' if year == `i' - 1
replace lgspilltec_roll1 = lgspilltec_ciq1`i' if year == `i' - 1
replace spillmaltec_roll = spillmaltec_ciq`i' if year == `i' - 1
replace lgspillmaltec_roll = lgspillmaltec_ciq`i' if year == `i' - 1
replace lgspillmaltec_roll1 = lgspillmaltec_ciq1`i' if year == `i' - 1
}

saveold spillover_final2, replace
